using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Collections;
using System.Data;

namespace DatabaseAccess
{
    public static class sqlDatabase
    {
        private static SqlConnection _conn = null;
        private static Hashtable colcnnstring = new Hashtable();
        internal static Hashtable colspinfo = new Hashtable();
        public static SqlDBExecution sqldbexec(string dbid)
        {
            const string functionname = "sqldbexec";
            try
            {
                string cnnstring = string.Empty;
                string xmldata = string.Empty;
                Hashtable spinfo = null;
                if (colcnnstring.Contains(dbid))
                    cnnstring = Convert.ToString(colcnnstring[dbid]);
                else
                {
                    cnnstring = LoadConnectionString(dbid);
                    colcnnstring.Add(dbid, cnnstring);
                }
                if (colspinfo.Contains(dbid))
                    spinfo = (Hashtable)(colspinfo[dbid]);
                else
                {
                    spinfo = LoadSPParaInfo(dbid, cnnstring);
                    colspinfo.Add(dbid, spinfo);
                }
                SqlDBExecution dbexec = new SqlDBExecution(cnnstring);
                dbexec.ParaInfo = spinfo;
                return dbexec;
            }
            catch (Exception ex)
            {
                Exception newexception = null;
                string errorsource = string.Empty;
                string errormessage = string.Empty;
                errorsource = "sqlDatabase." + functionname + "/" + ex.Source;
                errormessage = ex.Message;
                newexception = new Exception(errormessage);
                newexception.Source = errorsource;
                throw newexception;
            }
        }
        private static string LoadConnectionString(string dbid)
        {
            const string functionname = "LoadConnectionString";
            try
            {
                System.Collections.Specialized.NameValueCollection colvalues;
                string cnnstring = string.Empty;
                colvalues = System.Configuration.ConfigurationManager.AppSettings;
                cnnstring = colvalues.Get("connectionstring_" + dbid);
                return cnnstring;
            }
            catch (Exception ex)
            {
                Exception newexception = null;
                string errorsource = string.Empty;
                string errormessage = string.Empty;
                errorsource = "sqlDatabase." + functionname + "/" + ex.Source;
                errormessage = ex.Message;
                newexception = new Exception(errormessage);
                newexception.Source = errorsource;
                throw newexception;
            }
        }
        private static Hashtable LoadSPParaInfo(string dbid, string cnnstring)
        {
            const string functionname = "LoadSPParaInfo";
            SqlDataReader dr = null;
            try
            {
                string spprefix = "";
                string execspname = "";
                string spname = "";
                string prevspname = "";
                Hashtable splist = new Hashtable();
                StoredProcedure spobject = null;
                System.Collections.Specialized.NameValueCollection colvalues;
                colvalues = System.Configuration.ConfigurationManager.AppSettings;
                spprefix = colvalues.Get("spprefix_" + dbid) + "%";
                execspname = colvalues.Get("spname_" + dbid);
                dr = ExecuteSPParaInfo(cnnstring, execspname, spprefix);
                while (dr.Read())
                {
                    string paraname = "";
                    string datatype = "";
                    int size = 0;
                    bool isoutput = false;
                    Para paraobj = null;
                    spname = ((string)dr["SPName"]).ToLower();
                    if (!Convert.IsDBNull(dr["ParaName"]))
                    {
                        paraname = (string)dr["ParaName"];
                        datatype = (string)dr["ParaDataType"];
                        size = Convert.ToInt32(dr["ParaSize"]);
                        isoutput = Convert.ToBoolean(dr["ParaDirection"]);
                        paraobj = new Para();
                        paraobj.ParaName = paraname;
                        paraobj.Size = size;
                        paraobj.DataType = GetSQLDataType(datatype);
                        if (isoutput)
                            paraobj.ParaDirection = ParameterDirection.Output;
                        else
                            paraobj.ParaDirection = ParameterDirection.Input;
                    }
                    if ((prevspname == "") || (prevspname != spname))
                    {
                        if (spobject != null && prevspname != "")
                            splist.Add(spobject.SPName, spobject);
                        spobject = new StoredProcedure();
                        spobject.SPName = spname;
                        prevspname = spname;
                    }
                    if (paraobj != null && spobject != null)
                        spobject.AddParameter(paraobj);
                }
                if (spobject != null)
                    splist.Add(spobject.SPName, spobject);
                return splist;
            }
            catch (Exception ex)
            {
                Exception newexception = null;
                string errorsource = string.Empty;
                string errormessage = string.Empty;
                errorsource = "sqlDatabase." + functionname + "/" + ex.Source;
                errormessage = ex.Message;
                newexception = new Exception(errormessage);
                newexception.Source = errorsource;
                throw newexception;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                    dr = null;
                }
                if (_conn != null)
                {
                    _conn.Close();
                    _conn = null;
                }
            }
        }
        private static SqlDataReader ExecuteSPParaInfo(string cnnstring, string spname, string spnameprefix)
        {
            const string functionname = "ExecusteSPParaInfo";
            try
            {
                SqlDataReader dr = null;
                SqlParameter parameter = null;
                SqlCommand command = null;
                //SqlConnection conn = null;
                command = new SqlCommand(spname);
                command.CommandType = CommandType.StoredProcedure;
                parameter = new SqlParameter("@vcSpprefix", SqlDbType.VarChar, 100);
                parameter.Direction = 0;
                parameter.Value = spnameprefix;
                command.Parameters.Add(parameter);
                _conn = new SqlConnection(cnnstring);
                _conn.Open();
                command.Connection = _conn;
                dr = command.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                Exception newexception = null;
                string errorsource = string.Empty;
                string errormessage = string.Empty;
                errorsource = "sqlDatabase." + functionname + "/" + ex.Source;
                errormessage = ex.Message;
                newexception = new Exception(errormessage);
                newexception.Source = errorsource;
                throw newexception;
            }
        }
        private static SqlDbType GetSQLDataType(string datatype)
        {
            switch (datatype.ToLower())
            {
                case "bigint":
                    return SqlDbType.BigInt;
                case "binary":
                    return SqlDbType.Binary;
                case "bit":
                    return SqlDbType.Bit;
                case "char":
                    return SqlDbType.Char;
                case "datetime":
                    return SqlDbType.DateTime;
                case "decimal":
                    return SqlDbType.Decimal;
                case "float":
                    return SqlDbType.Float;
                case "image":
                    return SqlDbType.Image;
                case "int":
                    return SqlDbType.Int;
                case "money":
                    return SqlDbType.Money;
                case "nchar":
                    return SqlDbType.NChar;
                case "ntext":
                    return SqlDbType.NText;
                case "nvarchar":
                    return SqlDbType.NVarChar;
                case "real":
                    return SqlDbType.Real;
                case "smalldatetime":
                    return SqlDbType.SmallDateTime;
                case "smallint":
                    return SqlDbType.SmallInt;
                case "smallmoney":
                    return SqlDbType.SmallMoney;
                case "text":
                    return SqlDbType.Text;
                case "timestamp":
                    return SqlDbType.Timestamp;
                case "tinyint":
                    return SqlDbType.TinyInt;
                case "uniqueidentifier":
                    return SqlDbType.UniqueIdentifier;
                case "varbinary":
                    return SqlDbType.VarBinary;
                case "varchar":
                    return SqlDbType.VarChar;
                case "variant":
                    return SqlDbType.Variant;
                default:
                    // Default to Variant if anything else is supplied
                    return SqlDbType.Variant;
            }

        }
        
    }
}
